package com.loongtech.bi.support;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ExcelUtils {
	/**
	 * only parse 1st sheet and not parse 1st row
	 * 
	 * @param input
	 * @return
	 * @throws IOException
	 * @throws InvalidFormatException
	 * @date Jul 17, 2013
	 */
	public static List<List<String>> parseExcel(InputStream input)
			throws InvalidFormatException, IOException {
		// validate argument
		if (null == input)
			return null;

		List<List<String>> result = new LinkedList<List<String>>();
		Workbook wb = WorkbookFactory.create(input);
		// get 1st sheet
		Sheet tempSheet = wb.getSheetAt(0);

		Iterator<Row> iterator = tempSheet.rowIterator();
		while (iterator.hasNext()) {
			Row row = (Row) iterator.next();
			// not parse 1st row
			if (row.getRowNum() == 0)
				continue;

			Iterator<Cell> cellIterator = row.cellIterator();
			List<String> cellsOfRow = new ArrayList<String>();
			while (cellIterator.hasNext()) {
				Cell cell = (Cell) cellIterator.next();
				// make the list.size reach current size and fill the list with
				// ""
				if (cellsOfRow.size() < cell.getColumnIndex() + 1) {
					int currentSize = cellsOfRow.size();
					for (int i = 0; i < cell.getColumnIndex() + 1 - currentSize; i++)
						cellsOfRow.add("");
				}
				cellsOfRow.set(cell.getColumnIndex(), getCellValue(cell));
			}
			if (!cellsOfRow.isEmpty()) {
				result.add(cellsOfRow);
			}
		}
		return result;
	}


	/**
	 * get Excel-cell value as string
	 * 
	 * @param cell
	 * @return null cell return ""
	 * @author yuhongyong
	 * @date May 20, 2013
	 */
	public static String getCellValue(Cell cell) {
		String result = "";
		if (null == cell)
			return result;

		cell.setCellType(Cell.CELL_TYPE_STRING);
		result = cell.getStringCellValue().trim();
		return result;
	}

	/**
	 * clear temp_excel_dir
	 */
	public static void clearTempFile() {
		try {
			FileUtils.cleanDirectory(new File(""));
		} catch (Exception e) {
			System.err.println("meet error during clear temp_excel_dir");
		}
	}

	/**
	 * whether the row from some Excel is blank
	 * 
	 * @param row
	 * @return
	 * @date Sep 25, 2013
	 */
	public static boolean isBlankRow(Row row) {
		if (row == null)
			return true;

		Iterator<Cell> cellIterator = row.cellIterator();
		while (cellIterator.hasNext()) {
			Cell cell = (Cell) cellIterator.next();
			String value = getCellValue(cell);
			if (StringUtils.isNotBlank(value))
				return false;
		}
		return true;
	}
}
